package org.neuedu.dao;

import org.neuedu.bean.Article;
import org.neuedu.service.UserService;
import org.neuedu.service.UserServiceImpl;
import org.neuedu.utils.DBUtils;
import org.neuedu.bean.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ArticleDaoImpl implements ArticleDao {
    @Override
    public int saveArticleInfo(Article article) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;

        try{
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "insert into article(title,paykiss,content,cid,uid,updatetime) values(?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,article.getTitle());
            ps.setInt(2, article.getPayKiss());
            ps.setString(3, article.getContent());
            ps.setInt(4, article.getCid());
            ps.setInt(5, article.getUid());
            ps.setString(6, article.getUpdateTime());
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public List<Article> getTopArticleLiset() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select uid,avatar,nickname,catenameZh,title,DATE_FORMAT(publishtime,'%Y-%m-%d') publishtime,paykiss,isend,iscream,replynum,a.id aid\n" +
                    "from article a\n" +
                    "join user u\n" +
                    "on u.id = a.uid\n" +
                    "join category c\n" +
                    "on c.id = a.cid\n" +
                    "where istop = 1 order by publishtime desc";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Article article = new Article();
                article.setUid(rs.getInt("uid"));
                article.setCateNameZh(rs.getString("catenameZh"));
                article.setTitle(rs.getString("title"));
                article.setPublishTime(rs.getString("publishtime"));
                article.setPayKiss(rs.getInt("paykiss"));
                article.setEnd(rs.getBoolean("isend"));
                article.setCream(rs.getBoolean("iscream"));
                article.setReplyNum(rs.getInt("replynum"));
                article.setId(rs.getInt("aid"));
                User user = new User();
                user.setId(rs.getInt("uid"));
                user.setAvatar(rs.getString("avatar"));
                user.setNickName(rs.getString("nickname"));
                article.setUser(user);
                list.add(article);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }



    @Override
    public List<Article> getTenMainArticleList(Integer tid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select uid,avatar,nickname,catenameZh,title,DATE_FORMAT(publishtime,'%Y-%m-%d') publishtime,paykiss,isend,iscream,replynum,a.id aid\n" +
                    "from article a\n" +
                    "join user u\n" +
                    "on u.id = a.uid\n" +
                    "join category c\n" +
                    "on c.id = a.cid\n" +
                    "where istop != 1 ";
            if(tid == 1){
                sql = sql + "and isend = 0 ";
            }else if(tid == 2){
                sql = sql + "and isend = 1 ";
            }else if(tid == 3){
                sql = sql + "and iscream = 1 ";
            }
            sql = sql + "order by DATE_FORMAT(publishtime,'%Y-%m-%d %H:%i:%s') desc limit 0,10 ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Article article = new Article();
                article.setUid(rs.getInt("uid"));
                article.setCateNameZh(rs.getString("catenameZh"));
                article.setTitle(rs.getString("title"));
                article.setPublishTime(rs.getString("publishtime"));
                article.setPayKiss(rs.getInt("paykiss"));
                article.setEnd(rs.getBoolean("isend"));
                article.setCream(rs.getBoolean("iscream"));
                article.setReplyNum(rs.getInt("replynum"));
                article.setId(rs.getInt("aid"));
                User user = new User();
                user.setId(rs.getInt("uid"));
                user.setAvatar(rs.getString("avatar"));
                user.setNickName(rs.getString("nickname"));
                article.setUser(user);
                list.add(article);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public List<Article> getTopArticleLiset1() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "SELECT replynum,title,id FROM article WHERE YEARWEEK(date_format(publishtime,'%Y-%m-%d')) = YEARWEEK(now()) ORDER BY replynum desc LIMIT 0,10;\n";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Article article = new Article();
                article.setReplyNum(rs.getInt("replynum"));
                article.setTitle(rs.getString("title"));
                article.setId(rs.getInt("id"));
                list.add(article);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public List<Article> getbottomArticleLiset() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select avatar,a.id aid,nickname,title,replynum from article a join user u on u.id = a.uid join category c  on c.id = a.cid where istop != 1 ORDER BY replynum desc LIMIT 0,10;";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Article article = new Article();
                article.setReplyNum(rs.getInt("replynum"));
                article.setId(rs.getInt("aid"));
                User user = new User();
                user.setAvatar(rs.getString("avatar"));
                user.setNickName(rs.getString("nickname"));
                article.setUser(user);
                list.add(article);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }



    @Override
    public Article getArticleById(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Article article = null;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select a.id aid,title,cid,isend,istop,iscream,replynum,views,uid,paykiss,content,avatar,nickname,catenameZh,DATE_FORMAT(publishtime,'%Y-%m-%d') publishtime\n" +
                    "from article a\n" +
                    "join user u\n" +
                    "on u.id = a.uid\n" +
                    "join category c\n" +
                    "on c.id = a.cid\n" +
                    "where a.id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if (rs.next()) {
                article = new Article();
                article.setUid(rs.getInt("uid"));
                article.setCateNameZh(rs.getString("catenameZh"));
                article.setTitle(rs.getString("title"));
                article.setPublishTime(rs.getString("publishtime"));
                article.setPayKiss(rs.getInt("paykiss"));
                article.setEnd(rs.getBoolean("isend"));
                article.setCream(rs.getBoolean("iscream"));
                article.setTop(rs.getBoolean("istop"));
                article.setReplyNum(rs.getInt("replynum"));
                article.setViews(rs.getInt("views"));
                article.setId(rs.getInt("aid"));
                article.setCid(rs.getInt("cid"));
                article.setContent(rs.getString("content"));
                User user = new User();
                user.setId(rs.getInt("uid"));
                user.setAvatar(rs.getString("avatar"));
                user.setNickName(rs.getString("nickname"));
                article.setUser(user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return article;
    }
    @Override
    public List<Reply> getReplyByAid(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Reply> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select uid,nickname,avatar,DATE_FORMAT(replytime,'%Y-%m-%d') replytime,replycontent,accepted,goodnum\n" +
                    "from user u\n" +
                    "join reply r\n" +
                    "on u.id = r.uid\n" +
                    "where r.aid = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()) {
                Reply reply = new Reply();
                reply.setReplyTime(rs.getString("replytime"));
                reply.setReplyContent(rs.getString("replycontent"));
                reply.setAccepted(rs.getBoolean("accepted"));
                reply.setAid(id);
                reply.setGoodNum(rs.getInt("goodnum"));
                User user = new User();
                user.setId(rs.getInt("uid"));
                user.setNickName(rs.getString("nickname"));
                user.setAvatar(rs.getString("avatar"));
                reply.setUser(user);
                list.add(reply);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public int updateArticleViewsById(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update article set views = views + 1 where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }
    @Override
    public List<Article> getDhInfo(Integer cid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select cid,uid,avatar,nickname,catenameZh,title,DATE_FORMAT(publishtime,'%Y-%m-%d') publishtime,paykiss,istop,isend,iscream,replynum,a.id aid\n" +
                    "                    from article a\n" +
                    "                    join user u\n" +
                    "                    on a.uid = u.id\n" +
                    "                    join category c\n" +
                    "                    on c.id = a.cid" ;
            if(cid == 1){
                sql = sql + "where cid = 1";
            }else if(cid == 2){
                sql = sql + "where cid = 2";
            }else if(cid == 3){
                sql = sql + "where cid = 3";
            }else if(cid == 4){
                sql = sql + "where cid = 4";
            } else if(cid == 5){
                sql = sql + "where cid = 5";
            } else if(cid == 6){
                sql = sql + "where cid = 6";
            }
            sql = sql + " order by DATE_FORMAT(publishtime,'%Y-%m-%d %h:%i:%s') desc";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                Article article = new Article();
                User user = new User();
                article.setId(rs.getInt("aid"));
                article.setTitle(rs.getString("title"));
                article.setCid(rs.getInt("cid"));
                article.setTop(rs.getBoolean("istop"));
                article.setEnd(rs.getBoolean("isend"));
                article.setCream(rs.getBoolean("iscream"));
                article.setReplyNum(rs.getInt("replynum"));
                article.setUid(rs.getInt("uid"));
                article.setPayKiss(rs.getInt("paykiss"));
                article.setPublishTime(rs.getString("publishtime"));
                user.setId(rs.getInt("uid"));
                user.setNickName(rs.getString("nickname"));
                user.setAvatar(rs.getString("avatar"));
                article.setCateNameZh(rs.getString("catenameZh"));
                article.setUser(user);
                list.add(article);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
            DBUtils.getInstance().close(rs);
        }
        return list;
    }

}

